using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;
using Garlic.CC.Utility;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using Garlic.DM.DataModel;

namespace Garlic.DAL
{
    /// <summary>
    /// DataAccess
    /// </summary>
    public partial class CodeListDAL
    {
        #region Sql const
        public const string TC_BAS_CODELIST_SELECT_BY_ID = " SELECT code_cdescription,code_edescription,code_name,create_user,create_time,modify_user,modify_time" +
                                            " FROM TC_BAS_CodeList" +
                                            " WHERE 1=1 " +
                                            " and  code_name =  :code_name";


        public const string TC_BAS_CODELIST_SELECT = " SELECT " +
                                            "code_cdescription," +
                                            "code_edescription," +
                                            "code_name," +
                                            "create_user," +
                                            "create_time," +
                                            "modify_user," +
                                            "modify_time" +
                                            " FROM TC_BAS_CodeList {0}";



        public const string TC_BAS_CODELIST_INSERT = " INSERT INTO TC_BAS_CodeList (" +
                                            "code_cdescription," +
                                            "code_edescription," +
                                            "code_name," +
                                            "create_user," +
                                            "create_time," +
                                            "modify_user," +
                                            "modify_time" +
                                        ") VALUES (" +
                                            ":code_cdescription," +
                                            ":code_edescription," +
                                            ":code_name," +
                                            ":create_user," +
                                            ":create_time," +
                                            ":modify_user," +
                                            ":modify_time" +
                                        ")";


        public const string TC_BAS_CODELIST_UPDATE = " UPDATE TC_BAS_CodeList SET " +
                                            " code_cdescription =  :code_cdescription, " +
                                            " code_edescription =  :code_edescription, " +
                                            " create_user =  :create_user, " +
                                            " create_time =  :create_time, " +
                                            " modify_user =  :modify_user, " +
                                            " modify_time =  :modify_time " +
                                        " WHERE 1=1 " +
                                            " and  code_name =  :code_name";



        public const string TC_BAS_CODELIST_DELETE = " DELETE FROM TC_BAS_CodeList" +
                                        " WHERE 1=1" +
                                            " and  code_name =  :code_name";



        #endregion

        /// <summary>
        /// Get CodeListInfo by code_name
        /// </summary>
        /// <param name="code_name">CodeListInfo </param>
        /// <returns></returns>
        public static CodeListInfo GetInfo(string code_name)
        {

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(TC_BAS_CODELIST_SELECT_BY_ID);
            db.AddInParameter(dbCommand, "code_name", DbType.AnsiString, code_name);


            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                if (dr.Read())
                {
                    return CreateCodeListInfo(dr);
                }
                else
                    return null;
            }
        }


        /// <summary>
        /// Get data list
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns>CodeListInfo list </returns>
        public static List<CodeListInfo> GetList(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
            {
                query += " where " + textWhere;
            }

            if (!string.IsNullOrEmpty(orderText))
            {
                query += " order by " + orderText;
            }

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(TC_BAS_CODELIST_SELECT, query));

            return GetList(string.Format(TC_BAS_CODELIST_SELECT, query));

        }

        /// <summary>
        /// Get data list
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns>CodeListInfo list </returns>
        public static List<CodeListInfo> GetList(string sql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            List<CodeListInfo> list = new List<CodeListInfo>();
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    list.Add(CreateCodeListInfo(dr));
                }
            }

            return list;
        }


        /// <summary>
        /// Add
        /// </summary>
        /// <param name="codeListInfo"> entity</param>
        public static int Add(CodeListInfo codeListInfo)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(TC_BAS_CODELIST_INSERT);

            db.AddInParameter(dbCommand, "code_cdescription", DbType.AnsiString, codeListInfo.Code_cdescription);
            db.AddInParameter(dbCommand, "code_edescription", DbType.AnsiString, codeListInfo.Code_edescription);
            db.AddInParameter(dbCommand, "code_name", DbType.AnsiString, codeListInfo.Code_name);
            db.AddInParameter(dbCommand, "create_user", DbType.Int32, DBConvert.ToDBValue(codeListInfo.Create_user));
            db.AddInParameter(dbCommand, "create_time", DbType.DateTime, DBConvert.ToDBValue(codeListInfo.Create_time));
            db.AddInParameter(dbCommand, "modify_user", DbType.Int32, DBConvert.ToDBValue(codeListInfo.Modify_user));
            db.AddInParameter(dbCommand, "modify_time", DbType.DateTime, DBConvert.ToDBValue(codeListInfo.Modify_time));

            return int.Parse("0" + db.ExecuteScalar(dbCommand));
        }

        /// <summary>
        /// Update
        /// </summary>
        /// <param name="codeListInfo"> utility</param>
        public static void Update(CodeListInfo codeListInfo)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(TC_BAS_CODELIST_UPDATE);

            db.AddInParameter(dbCommand, "code_cdescription", DbType.AnsiString, codeListInfo.Code_cdescription);
            db.AddInParameter(dbCommand, "code_edescription", DbType.AnsiString, codeListInfo.Code_edescription);
            db.AddInParameter(dbCommand, "code_name", DbType.AnsiString, codeListInfo.Code_name);
            db.AddInParameter(dbCommand, "create_user", DbType.Int32, DBConvert.ToDBValue(codeListInfo.Create_user));
            db.AddInParameter(dbCommand, "create_time", DbType.DateTime, DBConvert.ToDBValue(codeListInfo.Create_time));
            db.AddInParameter(dbCommand, "modify_user", DbType.Int32, DBConvert.ToDBValue(codeListInfo.Modify_user));
            db.AddInParameter(dbCommand, "modify_time", DbType.DateTime, DBConvert.ToDBValue(codeListInfo.Modify_time));

            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="code_name">TC_BAS_CodeList Number</param>
        /// <returns></returns>
        public static void Delete(string code_name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(TC_BAS_CODELIST_DELETE);
            db.AddInParameter(dbCommand, "code_name", DbType.AnsiString, code_name);
            db.ExecuteNonQuery(dbCommand);
        }




        private static CodeListInfo CreateCodeListInfo(IDataReader rdr)
        {
            CodeListInfo mycodeListInfo = new CodeListInfo();
            mycodeListInfo.Code_cdescription = rdr.GetString(0);
            mycodeListInfo.Code_edescription = rdr.GetString(1);
            mycodeListInfo.Code_name = rdr.GetString(2);
            mycodeListInfo.Create_user = DBConvert.GetInt32(rdr, 3);
            mycodeListInfo.Create_time = DBConvert.GetDateTime(rdr, 4);
            mycodeListInfo.Modify_user = DBConvert.GetInt32(rdr, 5);
            mycodeListInfo.Modify_time = DBConvert.GetDateTime(rdr, 6);

            return mycodeListInfo;
        }
    }
}
